package faceattendsystem.daoImpl;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import faceattendsystem.dao.ISpecdayDao;
import faceattendsystem.entity.PageBean;
import faceattendsystem.entity.Specday;
import faceattendsystem.util.JDBCTemplate;
import faceattendsystem.util.JDBCTemplate.PreparedStatementSetter;
import faceattendsystem.util.JDBCTemplate.ResultSetCallBack;


public class SpecdayDaoImpl implements ISpecdayDao {

	@Override
	public int add(Specday specday) {
		String sql = "insert into specday values(?,?,?,?)";

		int result = JDBCTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setDate(1, specday.getSpecbdate());
				pstmt.setDate(2, specday.getSpecedate());
				pstmt.setString(3, specday.getDaytype());
				pstmt.setString(4, specday.getSpecname());
			}
		});

		return result;
	}

	@Override
	public int isRepeatDate(Date bdate) {
		String sql = "select count(*) from specday where ?>=specbdate and ?<=specedate";
		
		Number num = JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setDate(1, bdate);
				pstmt.setDate(2, bdate);
			}
		}, new ResultSetCallBack<Number>() {
			@Override
			public Number processRs(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		});
		
		return num!=null?num.intValue():0;
	}


	@Override
	public String queryByNowdate(Date nowdate) {
		String sql = "select daytype from specday where specbdate<=? and ?<=specedate";
		
		String daytype = JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setDate(1, nowdate);
				pstmt.setDate(2, nowdate);
			}
		}, new ResultSetCallBack<String>() {

			@Override
			public String processRs(ResultSet rs) throws SQLException {
				return rs.getString(1);
			}
		});
		
		return daytype;
	}
	
	@Override
	public PageBean<Specday> queryAll(int pc, int ps) {
		PageBean<Specday> pb = new PageBean<Specday>();
		pb.setPc(pc);
		pb.setPs(ps);

		String sql = "select count(*) from specday";

		Number num = JDBCTemplate.singleQuery(sql, null, new ResultSetCallBack<Number>() {
			@Override
			public Number processRs(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		});

		int tr = 1;
		if (num != null) {
			tr = num.intValue();
		}
		pb.setTr(tr);

		sql = "select * from specday order by specbdate desc limit ?,?";

		List<Specday> specdayList = JDBCTemplate.query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setInt(1, (pc - 1) * ps);
				pstmt.setInt(2, ps);
			}
		}, new ResultSetCallBack<Specday>() {
			@Override
			public Specday processRs(ResultSet rs) throws SQLException {
				Specday specday = new Specday();
				specday.setSpecbdate(rs.getDate(1));
				specday.setSpecedate(rs.getDate(2));
				specday.setDaytype(rs.getString(3));
				specday.setSpecname(rs.getString(4));
				return specday;
			}
		});

		pb.setBeanList(specdayList);
		return pb;
	}

	@Override
	public PageBean<Specday> query(Specday specday, int pc, int ps) {
		PageBean<Specday> pb = new PageBean<Specday>();
		pb.setPc(pc);
		pb.setPs(ps);
		
		Date specbdate = specday.getSpecbdate();
//		Date specedate = specday.getSpecedate();
//		String specname = specday.getSpecname();
//		String type = specday.getType();

		String sql = "select count(*) from specday where specbdate<=? and specedate>=?";
		
		Number num = JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setDate(1, specbdate);
				pstmt.setDate(2, specbdate);
			}
		}, new ResultSetCallBack<Number>() {
			@Override
			public Number processRs(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		});
		
		int tr = 1;
		if(num!=null){
			tr=num.intValue();
		}
		pb.setTr(tr);
		
		sql = "select * from specday where specbdate<=? and specedate>=? order by specbdate desc limit ?,?";
		List<Specday> specdayList = JDBCTemplate.query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setDate(1, specbdate);
				pstmt.setDate(2, specbdate);
				pstmt.setInt(3, (pc-1)*ps);
				pstmt.setInt(4, ps);
			}
		}, new ResultSetCallBack<Specday>() {
			@Override
			public Specday processRs(ResultSet rs) throws SQLException {
				Specday specday = new Specday();
				specday.setSpecbdate(rs.getDate(1));
				specday.setSpecedate(rs.getDate(2));
				specday.setDaytype(rs.getString(3));
				specday.setSpecname(rs.getString(4));
				return specday;
			}
		});
		
		pb.setBeanList(specdayList);
		return pb;
	}
}
